News Center

列數據轉換成按行展示的sql該怎么寫？

今天朋友問到一個有趣的問題，直接把我難住了

下面是我給出的方法 ，這個不是準確答案

#新建測試表，添加數據

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`f1` varchar(255) DEFAULT NULL,
`f2` varchar(255) DEFAULT NULL,
`f3` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', '字段1', '字段2', '字段3');
INSERT INTO `test` VALUES ('2', '字段11', '字段22', '字段33');

#硬寫查詢

select col, sum(ifnull(c1,0)) as c1,sum(ifnull(c2,0)) as c2 from (
select col, case id when 1 then val end AS c1,case id when 2 then val end AS c2 from(
select 'f1' as col, f1 as val, id from test
union all
select 'f2' as col, f2 as val, id from test
union all
select 'f3' as col, f3 as val, id from test
)t order by col)s group by col;

#過程方法

SET @AA='';
SET @BB='';
SET @CC='';
SET @str_aa='';
SET @str_bb='';
SET @str_cc='';
SELECT @AA:=CONCAT(@AA,'sum(ifnull(c',id,',0)) as c',id,',') as aa into @str_aa FROM (SELECT DISTINCT id FROM test) A order by length(aa) desc limit 1;
SELECT @BB:=CONCAT(@BB,'case id when ',id,' then val end as c',id,',') as bb into @str_bb FROM (SELECT DISTINCT id FROM test) B order by length(bb) desc limit 1;
SELECT @CC:=CONCAT(@CC,'select \'',col,'\' as col, ',col,' as val, id from test union all ') as cc into @str_cc FROM (SELECT COLUMN_NAME as col FROM information_schema.columns WHERE table_name='test' HAVING col!='id') C order by length(cc) desc limit 1;

SET @DD=CONCAT('select col,',LEFT(@str_aa,char_length(@str_aa)-1),' from (select col,',LEFT(@str_bb,char_length(@str_bb)-1),' from(',LEFT(@str_cc,char_length(@str_cc)-10),')t order by col)s group by col');
PREPARE stmt FROM @DD;
EXECUTE stmt ;
deallocate prepare stmt;

山西更新科技屌絲程序